package com.jp.fm.dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import net.sf.ehcache.Cache;
import net.sf.ehcache.Element;

import com.jp.fm.HomeFinanceRunner;
import com.jp.fm.data.Budget;
import com.jp.fm.data.Transaction;

public class BudgetDao {

	private static final String COLUMN_CATEGORY_ID = "CATEGORY_ID";
	private static final String COLUMN_REPEAT = "REPEAT";
	private static final String COLUMN_AMOUNT = "AMOUNT";
	private static final String COLUMN_TYPE = "TYPE";
	private static final String COLUMN_DATE = "DATE";
	private static final String COLUMN_ID = "ID";

	public static final String CACHE_NAME = "Budget";

	private TransactionDao transactionDao = null;

	public List<Budget> findByDate(final Date date) {

		List<Budget> budgets = findAll();
		List<Budget> ret = new ArrayList<Budget>();

		Budget budget = null;

		Calendar calStartMonth = new GregorianCalendar();
		calStartMonth.setTime(date);
		calStartMonth.set(Calendar.DAY_OF_MONTH, 1);
		calStartMonth.add(Calendar.DAY_OF_MONTH, -1);
		calStartMonth.set(Calendar.HOUR_OF_DAY,
				calStartMonth.getMaximum(Calendar.HOUR_OF_DAY));
		calStartMonth.set(Calendar.MINUTE,
				calStartMonth.getMaximum(Calendar.MINUTE));
		calStartMonth.set(Calendar.SECOND,
				calStartMonth.getMaximum(Calendar.SECOND));

		Calendar calEndMonth = new GregorianCalendar();
		calEndMonth.setTime(date);
		calEndMonth.set(Calendar.DAY_OF_MONTH,
				calEndMonth.getMaximum(Calendar.DAY_OF_MONTH));
		// calEndMonth.add(Calendar.DAY_OF_MONTH, 1);
		calEndMonth.set(Calendar.HOUR_OF_DAY,
				calEndMonth.getMaximum(Calendar.HOUR_OF_DAY));
		calEndMonth.set(Calendar.MINUTE,
				calEndMonth.getMaximum(Calendar.MINUTE));
		calEndMonth.set(Calendar.SECOND,
				calEndMonth.getMaximum(Calendar.SECOND));

		for (Iterator<Budget> iterator = budgets.iterator(); iterator.hasNext();) {

			budget = iterator.next();

			if (budget.getDate().after(calStartMonth.getTime())
					&& budget.getDate().before(calEndMonth.getTime())) {

				List<Transaction> trans = transactionDao.findByAny(
						budget.getCategory(), calStartMonth.getTime(),
						calEndMonth.getTime(), null, null, null);

				Float amountCount = Float.valueOf("0");
				for (Iterator<Transaction> iterator2 = trans.iterator(); iterator2
						.hasNext();) {
					Transaction transaction = iterator2.next();

					amountCount += transaction.getAmount();
				}

				budget.setCurrentAmount(amountCount);

				ret.add(budget);
			}
		}

		return ret;
	}

	public List<Budget> findAll() {

		List<Budget> ret = new ArrayList<Budget>();
		Budget budget = null;
		CategoryDao categoryDao = new CategoryDao();

		// look in cache first
		Cache cache = HomeFinanceRunner.getCache(CACHE_NAME);
		Map<Object, Element> elementMap = cache.getAll(cache
				.getKeysWithExpiryCheck());
		if (null == elementMap || elementMap.size() == 0) {

			Connection c = null;
			PreparedStatement p = null;
			ResultSet rs = null;

			try {
				c = HomeFinanceRunner.getConnection();

				p = c.prepareStatement("select id, date, amount, type, category_id, repeat "
						+ "from budget");

				rs = p.executeQuery();

				while (rs.next()) {

					budget = new Budget(rs.getInt(COLUMN_ID),
							rs.getDate(COLUMN_DATE),
							rs.getFloat(COLUMN_AMOUNT),
							rs.getString(COLUMN_REPEAT),
							rs.getString(COLUMN_TYPE), categoryDao.findById(rs
									.getInt(COLUMN_CATEGORY_ID)));

					ret.add(budget);
					cache.put(new Element(budget.getId(), budget));

				}
			} catch (SQLException e) {

				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				HomeFinanceRunner.closeConnection(c, p, rs);
			}
		} else {

			for (Iterator<Element> iterator = elementMap.values().iterator(); iterator
					.hasNext();) {
				Element element = iterator.next();

				budget = (Budget) element.getObjectValue();
				ret.add(budget);
			}
		}

		return ret;
	}

	public final Integer writeObject(final Budget budget) {
		Connection c = null;
		PreparedStatement p = null;
		ResultSet rs = null;
		Integer ret = null;

		try {
			c = HomeFinanceRunner.getConnection();

			p = c.prepareStatement("insert into budget (type, amount, category_id, "
					+ "date, repeat) VALUES(?,?,?,?,?)");
			p.setString(HomeFinanceRunner.DB_COLUMN_FIRST, budget.getType());
			p.setFloat(HomeFinanceRunner.DB_COLUMN_SECOND, budget.getAmount());
			p.setInt(HomeFinanceRunner.DB_COLUMN_THIRD, budget.getCategory()
					.getId());
			p.setDate(HomeFinanceRunner.DB_COLUMN_FOURTH, new java.sql.Date(
					budget.getDate().getTime()));
			p.setString(HomeFinanceRunner.DB_COLUMN_FIFTH,
					budget.getRepeatInterval());
			p.executeUpdate();
			c.commit();

			p.close();

			// Get the generated ID
			p = c.prepareStatement("CALL IDENTITY()");
			rs = p.executeQuery();
			rs.next();
			int identity = rs.getInt(1);

			ret = identity;

			// update cache
			Cache cache = HomeFinanceRunner.getCache(CACHE_NAME);
			cache.removeAll();

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			HomeFinanceRunner.closeConnection(c, p, rs);
		}

		return ret;
	}

	public final void delete(final Budget budget) {
		Connection c = null;
		PreparedStatement p = null;
		ResultSet rs = null;

		try {
			c = HomeFinanceRunner.getConnection();

			p = c.prepareStatement("delete from budget where id = ?");
			p.setInt(1, budget.getId());

			p.executeUpdate();

			c.commit();

			// clear cache
			Cache cache = HomeFinanceRunner.getCache(CACHE_NAME);
			cache.removeAll();

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			HomeFinanceRunner.closeConnection(c, p, rs);
		}
	}

	public void copyBudgetsFromLastMonth() {
		Connection c = null;
		PreparedStatement p = null;
		ResultSet rs = null;

		// Get the first and last date of the month
		Date now = new Date();
		Calendar calStartMonth = new GregorianCalendar();
		calStartMonth.setTime(now);
		calStartMonth.add(Calendar.MONTH, -1);
		calStartMonth.set(Calendar.DAY_OF_MONTH, 1);

		Calendar calEndMonth = new GregorianCalendar();
		calEndMonth.setTime(now);
		calEndMonth.add(Calendar.MONTH, -1);
		calEndMonth.set(Calendar.DAY_OF_MONTH,
				calEndMonth.getMaximum(Calendar.DAY_OF_MONTH));

		try {
			c = HomeFinanceRunner.getConnection();

			p = c.prepareStatement("insert into budget (DATE, TYPE, "
					+ "CATEGORY_ID, AMOUNT, REPEAT) "
					+ "SELECT CURRENT_TIMESTAMP, TYPE, CATEGORY_ID, "
					+ "AMOUNT, REPEAT FROM BUDGET where date >= ? and date <= ?");
			p.setDate(1, new java.sql.Date(calStartMonth.getTime().getTime()));
			p.setDate(2, new java.sql.Date(calEndMonth.getTime().getTime()));
			p.executeUpdate();
			c.commit();

			// update cache
			Cache cache = HomeFinanceRunner.getCache(BudgetDao.CACHE_NAME);
			cache.removeAll();

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			HomeFinanceRunner.closeConnection(c, p, rs);
		}
	}

	public final void setTransactionDao(final TransactionDao transactionDao) {
		this.transactionDao = transactionDao;
	}

}
